package com.manage.dao.impl;

import com.manage.dao.HousingDao;
import com.manage.pojo.Housing;
import com.manage.utils.DateUtils;
import com.manage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class HousingDaoImpl implements HousingDao {

    @Override
    public List<Housing> findAll() throws SQLException {

        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        String sql="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid GROUP BY a.hid";

        List<Housing> query = qr.query(sql, new ResultSetHandler<List<Housing>>() {
            @Override
            public List<Housing> handle(ResultSet resultSet) throws SQLException {
                List<Housing> list = new ArrayList<Housing>();
                while (resultSet.next()) {
                    Housing housing = new Housing();
                    housing.setHid(resultSet.getInt("hid"));
                    housing.setHnumber(resultSet.getString("hnumber"));
                    housing.setHname(resultSet.getString("hname"));
                    housing.setHplace(resultSet.getString("hplace"));
                    housing.setPlacearea(resultSet.getDouble("placearea"));
                    housing.setBcount(resultSet.getInt("bcount"));
                    housing.setRcount(resultSet.getInt("rcount"));
                    housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                    housing.setHimage(resultSet.getString("himage"));
                    housing.setDeveloper(resultSet.getString("developer"));
                    housing.setProperty(resultSet.getString("property"));
                    housing.setHhiredate(resultSet.getDate("hhiredate"));
                    list.add(housing);
                }
                return list;
            }
        });

        return query;
    }

    @Override
    public List<Housing> findAll(int page, int limit) throws SQLException {
            QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
            String sql="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                    " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid GROUP BY a.hid limit ?,?";
            Object[] args={(page-1)*limit,limit};
            List<Housing> query = qr.query(sql, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            },args);

            return query;
        }

    @Override
    public int deleteHousingByHid(int hid) throws SQLException {
        QueryRunner qr=new QueryRunner();


            JDBCUtils.begin();
            String sql="DELETE FROM housing WHERE hid=?";
            int update = qr.update(JDBCUtils.getConn(), sql, hid);

            return update;

    }

    @Override
    public int insertHousing(Housing housing) throws SQLException {
        QueryRunner qr=new QueryRunner();
        Object[] args = {housing.getHnumber(),housing.getHname(),housing.getHplace(),housing.getPlacearea(),housing.getGreeningrate(),housing.getHimage(),housing.getDeveloper(),housing.getProperty(),housing.getHhiredate()};
        int update = qr.update(JDBCUtils.getConn(), "insert into housing values (null,?,?,?,?,?,?,?,?,?)", args);
        return update;
    }

    @Override
    public Housing selectOne(int hid) throws SQLException{
        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        Housing query = qr.query("select * from housing where hid=?", new BeanHandler<Housing>(Housing.class), hid);
        return query;
    }

    @Override
    public int updateEmp(Housing housing) throws SQLException {
        QueryRunner qr=new QueryRunner();
        Object[] args = {housing.getHnumber(),housing.getHname(),housing.getHplace(),housing.getPlacearea(),housing.getGreeningrate(),housing.getHimage(),housing.getDeveloper(),housing.getProperty(),housing.getHhiredate(),housing.getHid()};

       return qr.update(JDBCUtils.getConn(),"UPDATE housing SET hnumber=?,hname=?,hplace=?,placearea=?,greeningrate=?,himage=?,developer=?,property=?,hhiredate=? WHERE hid=?",args);
    }



    @Override
    public List<Housing> selectBySearch(int page, int limit, String hname, String hhiredate) throws SQLException{

        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        String sql1="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where a.hname like ? and a.hhiredate between ? and ?  GROUP BY a.hid limit ?,?";
        String sql2="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where a.hname like ?  GROUP BY a.hid limit ?,?";
        String sql3="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where  a.hhiredate between ? and ?  GROUP BY a.hid limit ?,?";


        Object[]  args2={"%"+hname+"%",(page-1)*limit,limit};

        if (hname!=null&&!hname.isEmpty()&&hhiredate!=null&&!hhiredate.isEmpty()){
            String[] split = hhiredate.split(" 到 ");
            Date startDate = DateUtils.stringToDate(split[0], "yyyy年MM月dd日");
            Date endDate = DateUtils.stringToDate(split[1], "yyyy年MM月dd日");
            Object[]  args1={"%"+hname+"%",startDate,endDate,(page-1)*limit,limit};
            List<Housing> query = qr.query(sql1, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args1);

            return query;
        }
        if (hname!=null&&!hname.isEmpty()&&hhiredate!=null&&hhiredate.isEmpty()){
            List<Housing> query = qr.query(sql2, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args2);
            return query;
        }
        if (hname!=null&&hname.isEmpty()&&hhiredate!=null&&!hhiredate.isEmpty()){
            String[] split = hhiredate.split(" 到 ");
            Date startDate = DateUtils.stringToDate(split[0], "yyyy年MM月dd日");
            Date endDate = DateUtils.stringToDate(split[1], "yyyy年MM月dd日");
            Object[]  args3={startDate,endDate,(page-1)*limit,limit};
            List<Housing> query = qr.query(sql3, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args3);
            return query;
        }
        else{
            String sql="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                    " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid GROUP BY a.hid limit ?,?";

            List<Housing> query = qr.query(sql, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            },((page-1)*limit),limit);
            return query;
        }
    }

    @Override
    public int selectBySearchCount(String hname, String hhiredate) throws SQLException {

        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        String sql1="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where a.hname like ? and a.hhiredate between ? and ? GROUP BY a.hid";
        String sql2="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where a.hname like ?  GROUP BY a.hid";
        String sql3="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid where  a.hhiredate between ? and ?  GROUP BY a.hid";


        Object[]  args2={"%"+hname+"%"};

        if (hname!=null&&!hname.isEmpty()&&hhiredate!=null&&!hhiredate.isEmpty()){
            String[] split = hhiredate.split(" 到 ");
            Date startDate = DateUtils.stringToDate(split[0], "yyyy年MM月dd日");
            Date endDate = DateUtils.stringToDate(split[1], "yyyy年MM月dd日");
            Object[]  args1={"%"+hname+"%",startDate,endDate};
            List<Housing> query = qr.query(sql1, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args1);

            return query.size();
        }
        if (hname!=null&&!hname.isEmpty()&&hhiredate!=null&&hhiredate.isEmpty()){
            List<Housing> query = qr.query(sql2, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args2);
            return query.size();
        }
        if (hname!=null&&hname.isEmpty()&&hhiredate!=null&&!hhiredate.isEmpty()){
            String[] split = hhiredate.split(" 到 ");
            Date startDate = DateUtils.stringToDate(split[0], "yyyy年MM月dd日");
            Date endDate = DateUtils.stringToDate(split[1], "yyyy年MM月dd日");
            Object[]  args3={startDate,endDate};
            List<Housing> query = qr.query(sql3, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<Housing>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            }, args3);
            return query.size();
        }
        else{
            String sql="SELECT *,COUNT(r.rid)AS rcount FROM(SELECT h.hid,h.hnumber,h.hname,hplace,placearea,COUNT(b.bid) AS bcount,h.greeningrate,h.himage,h.developer,h.property,h.hhiredate\n" +
                    " FROM housing AS h LEFT JOIN building AS b ON h.hid=b.hid   GROUP BY h.hid)AS a LEFT JOIN room AS r ON a.hid=r.hid GROUP BY a.hid";

            List<Housing> query = qr.query(sql, new ResultSetHandler<List<Housing>>() {
                @Override
                public List<Housing> handle(ResultSet resultSet) throws SQLException {
                    List<Housing> list = new ArrayList<>();
                    while (resultSet.next()) {
                        Housing housing = new Housing();
                        housing.setHid(resultSet.getInt("hid"));
                        housing.setHnumber(resultSet.getString("hnumber"));
                        housing.setHname(resultSet.getString("hname"));
                        housing.setHplace(resultSet.getString("hplace"));
                        housing.setPlacearea(resultSet.getDouble("placearea"));
                        housing.setBcount(resultSet.getInt("bcount"));
                        housing.setRcount(resultSet.getInt("rcount"));
                        housing.setGreeningrate(resultSet.getDouble("greeningrate"));
                        housing.setHimage(resultSet.getString("himage"));
                        housing.setDeveloper(resultSet.getString("developer"));
                        housing.setProperty(resultSet.getString("property"));
                        housing.setHhiredate(resultSet.getDate("hhiredate"));
                        list.add(housing);
                    }
                    return list;
                }
            });
            return query.size();
        }
    }


}
